package com.ccit.dao;

import com.ccit.domain.Attendance;
import com.ccit.domain.AttendanceType;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface AttendanceDao {

    @Insert("insert into Attendance (employeeId,employeeName,checkIn,checkOut, checkDate,status) values(#{employeeId},#{employeeName},#{checkIn},#{checkOut},#{checkDate},#{status})")
    public int add(AttendanceType attendance);

    //通过Id查询员工信息
    @Select("select * from attendance where employeeId = #{employeeId} ORDER BY checkIn desc")
    public List<Attendance> getById(Integer id);

    //查询所有员工信息
    @Select("select * from attendance")
    public List<Attendance> selectAll();

    //查询近30天出勤记录
    @Select("SELECT * FROM Attendance where employeeId = #{employeeId} ORDER BY checkDate DESC LIMIT 30")
    public List<AttendanceType> selectByDays(Integer id);

    //根据ID删除
    @Delete("delete from attendance where employeeId = #{employeeId}")
    public int delete(Integer id);

    @Delete({
            "DELETE FROM attendance",
            "WHERE employeeId IN",
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>"
    })
    public int deleteByIds(List<Integer> ids);

    //修改
    @Update("UPDATE attendance SET  checkOut = #{checkOut} ,status = #{status} WHERE employeeId = #{employeeId} And checkDate = #{checkDate}")
    public int update(AttendanceType attendance);

    /**
     * 分页查询
     * @param begin
     * @param size
     * @return
     */
    @Select("SELECT *\n" +
            "FROM attendance\n" +
            "ORDER BY checkIn asc limit #{begin} , #{size}")
    List<Attendance> selectByPage(@Param("begin") int begin, @Param("size") int size);

    /**
     * 分页条件查询
     */
    @Select("SELECT * FROM Attendance WHERE 1=1 " +
            "AND employeeName LIKE CONCAT('%', #{attendance.employeeName}, '%') " +
//            "AND (checkIn LIKE CONCAT('%', #{attendance.checkIn}, '%') OR #{attendance.checkIn} IS NULL) " +
//            "AND (checkOut LIKE CONCAT('%', #{attendance.checkOut}, '%') OR #{attendance.checkOut} IS NULL) " +
            "LIMIT #{begin}, #{size}")
    List<Attendance> selectByPageAndCondition(@Param("attendance") Attendance attendance, @Param("begin") int begin, @Param("size") int size);
    /**
     * 查询总记录数
     * @return
     */
    @Select("select count(*) from attendance ")
    int selectTotalCount();

    /**
     * 根据条件查询总记录数
     * @return
     */
    @Select("SELECT COUNT(*) FROM Attendance WHERE employeeName LIKE CONCAT('%', #{attendance.employeeName}, '%') " +
            "AND checkIn LIKE CONCAT('%', #{attendance.checkIn}, '%') " +
            "AND checkOut LIKE CONCAT('%', #{attendance.checkOut}, '%')")
    int selectTotalCountByCondition(@Param("attendance") Attendance attendance);

    /**
     * 根据ID和日期查询数据
     *
     */
    //通过Id查询员工信息
    @Select("select * from Attendance where employeeId = #{employeeId} and checkDate = #{checkDate}")
    AttendanceType findByEmployeeIdAndDate(  Integer employeeId, String checkDate);

    /**
     * 根据日期查询数据
     * @return List<AttendanceType>
     */
    //通过Id查询员工信息
    @Select("SELECT * FROM Attendance WHERE checkDate = #{checkDate}")
    List<AttendanceType> findByEmployeeDate(@Param("checkDate") String checkDate);


    /**
     * 根据ID和日期查询数据，返回当天未打卡的员工id
     *
     */
    //查询没有出勤记录的员工的ID和姓名。使用左连接将 "Employee" 表与 "attendance" 表连接，
    // 并通过判断 "attendance" 表的记录是否为 NULL 来确定没有出勤记录的员工
    @Select("SELECT e.id AS employeeId,e.employeeName AS employeeName\n" +
            "FROM Employee e\n" +
            "LEFT JOIN attendance a ON e.id = a.employeeId AND DATE(a.checkDate) = CURDATE()\n" +
            "WHERE a.id IS NULL;")
    List<AttendanceType>  selectByEmployeeIdAndDate();





}
